﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public  class WordQuestionEntityDal
    {
        private readonly SQLHelper sqlhelper = null;

        public WordQuestionEntityDal()
        {
            sqlhelper =new SQLHelper(); 
        }

        #region LoadWordByPaperType 根据试卷类型选择题库中的所有题信息 2015年12月21日 李少然
        /// <summary>
        /// 根据试卷类型选择题库中的所有题信息
        /// </summary>
        /// <param name="studentinfo">试卷类型</param>
        /// <returns>返回题目的内容</returns>
        public DataTable LoadWordByPaperType(string PaperType)
        {
            DataTable dt ;
            string sql = "select QuestionID,QuestionFlag,QuestionContent,Fration, PaperType,RightAnswer,QuestionParameter from  WordQuestionEntity  where PaperType=@PaperType ";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@PaperType",PaperType )              
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 利用全局变量，从题库中加载word试题--周洲--2015年11月21日
        /// <summary>
        /// 利用全局变量，从题库中加载word试题--周洲--2015年11月21日
        /// </summary>
        /// <param name="studentinfo">传递学生号</param>
        /// <returns>返回题目的内容</returns>
        public DataTable LoadWordQuestion(WordQuestionEntity wordinfo)
        {
            DataTable dt ;
            string sql = "select QuestionID,QuestionFlag,QuestionContent,Fration, PaperType,RightAnswer from  WordQuestionEntity  where PaperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@PaperType",wordinfo .PaperType ),
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion


        #region 将答案和分数更新到数据库中  李少然 2015年12月26日10:09:07
        /// <summary>
        /// 将答案和分数更新到数据库中
        /// </summary>
        /// <param name="list">StudentID,QuestionID,ExamAnswer,TimeStamp,fration</param>
        /// <param name="studentrecord">只需要一个studentID，为了查出学院ID</param>
        public void ReturnScore(List<WordQuestionRecordEntity> list, WordQuestionRecordEntity studentrecord)
        {
            String which = WhichWordRecored(studentrecord);
            StringBuilder sbSql = new StringBuilder();
            sbSql.Append("update WordQuestionRecordEntity_" + which + " SET Fration = CASE QuestionID ");
            string Fation = string.Empty;
            string questionID = string.Empty;
            string studentIDs = string.Empty;
            string tamstamp = string.Empty;

            //根据学号，和时间戳选出这一段的内容，然后再根据QuestionID给每一个flag更新分数。
            #region 更新分数的拼接sql语句
            for (int i = 0; i < list.Count; i++)
            {
                questionID = list[i].QuestionID.ToString();
                //将分数传给变量
                Fation = list[i].Fration;
                //判分sql，拼接sql，用case，when语句
                sbSql.Append(" WHEN '" + questionID + "' THEN '" + Fation + "'");
            }
            sbSql.Append("end,"); //加“，” 
            #endregion

            #region 拼接考生答案更新到数据库中
            //拼接更新考生答案sql
            sbSql.Append("  ExamAnswer = CASE QuestionID");
            for (int i = 0; i < list.Count; i++)
            {
              
                questionID = list[i].QuestionID.ToString();
                //学生答案信息更新，when后边的是QuerstionID,
                sbSql.Append(" WHEN '" + questionID + "' THEN '" + list[i].ExamAnswer + "'");
            }
            sbSql.Append(" end");   //不加逗号“，” 
            #endregion


           studentIDs += "'" + list[0].StudentID.ToString() + "'";
           tamstamp += "'" + list[0].TimeStamp.ToString() + "'";

            studentIDs.Remove(studentIDs.Length - 1, 1);
            sbSql.Append(" where StudentID in (" + studentIDs + ") and TimeStamp in (" + tamstamp + ")");
            DataTable dt = sqlhelper.ExecuteQuery(sbSql.ToString(), CommandType.Text);

        } 
        #endregion


        #region 根据学号选择题型  李少然
        /// <summary>
        /// 根据学号选择题型
        /// </summary>
        /// <param name="studentinfo">传递学生号</param>
        /// <returns>试卷类型</returns>
        public DataTable SelectPaperTypeByStudentID(StudentInfoEntity studentinfo)
        {
            DataTable dt ;
            string sql = "select PaperType from WordQuestionRecordEntity where StudentID = @studentinfo";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@studentinfo",studentinfo.studentID )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;

        }
        #endregion


        #region 查找所有的word的套卷 WordPaperType() 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        /// 查找所有的word的套卷
        /// </summary>
        /// <returns></returns>
        public DataTable WordPaperType()
        {
            DataTable dt ;
            string sql = "select Distinct PaperType from WordQuestionEntity";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion

        #region 给WordQutionEntity分组 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        /// 查找所有的word的套卷
        /// </summary>
        /// <returns></returns>
        public DataTable WordPaperTypeGroupByPaperType()
        {
            DataTable dt ;
            string sql = "select * from WordQuestionEntity group by PaperType";
            dt = sqlhelper.ExecuteQuery(sql, CommandType.Text);
            return dt;
        }
        #endregion


        #region 根据PaperType查询分组 邱慕夏 2015年11月20日16:57:30
        /// <summary>
        ///给WordQutionEntity分组
        /// </summary>
        /// <returns></returns>
        public DataTable WordPaperTypeGroupByPaperType(String PaperType)
        {
            DataTable dt;
            string sql = "select * from WordQuestionEntity where PaperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[]{
                new  SqlParameter("@PaperType",PaperType )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion


        #region 根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏  2015年11月23日14:28:57
        /// <summary>
        /// 根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public int InsertWordRecord(WordQuestionRecordEntity studentrecord)
        {
            String which = WhichWordRecored(studentrecord);
            string sql = "Insert into WordQuestionRecordEntity_" + which + "(QuestionID,StudentID,PaperType,QuestionContent,RightAnswer) values(@QuestionID,@StudentID,@PaperType,@QuestionContent,@RightAnswer)";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.StudentID ),
                new SqlParameter ("@PaperType",studentrecord.PaperType ),
                new SqlParameter ("@QuestionContent",studentrecord.QuestionContent),
                new SqlParameter ("@RightAnswer",studentrecord.RightAnswer),
                new SqlParameter ("@QuestionID",studentrecord.QuestionID )
            };
            int flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
            return flag;
        }
        #endregion


        #region 根据学生的ID查询去重--邱慕夏  2015年11月23日14:28:57
        /// <summary>
        /// 根据学生的ID查询去重--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否已经添加</returns>
        public Boolean SelectWordRecord(WordQuestionRecordEntity studentrecord)
        {
            String which = WhichWordRecored(studentrecord);
            string sql = "Select * from  WordQuestionRecordEntity_" + which + " where StudentID=@studentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.StudentID ),
            };
            DataTable dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            if (dt.Rows.Count == 0)
            {
                return true;
            }
            else {
                return false;
            }
        }
        #endregion


        #region 批量根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// <summary>
        /// 批量根据学生的ID查询是否该学生是要往哪个表中进行insert--邱慕夏
        /// </summary>
        /// <param name="studentinfo">根据CollegeID判断学生在那个表中</param>
        /// <returns>是否添加成功</returns>
        public int InsertWordRecordList(List<WordQuestionRecordEntity> studentrecordlist, StudentInfoEntity student)
        {
            int flag = 0;


           
      
           

         
            if (studentrecordlist.Count != 0)
            {
                String which = student.CollegeID.Trim();
                string sql = "Insert into WordQuestionRecordEntity_" + which + "(QuestionID,StudentID,PaperType,QuestionContent,RightAnswer,TimeStamp) ";
                for (int i = 0; i < studentrecordlist.Count; i++)
                {
                    if (i != studentrecordlist.Count - 1)
                    {
                        sql = sql + "select '" + studentrecordlist[i].QuestionID + "','" + studentrecordlist[i].StudentID + "','" + studentrecordlist[i].PaperType + "','" + studentrecordlist[i].QuestionContent + "','" + studentrecordlist[i].RightAnswer + "','" + studentrecordlist[i] .TimeStamp + "' union all ";
                    }
                    else
                    {
                        sql = sql + "select '" + studentrecordlist[i].QuestionID + "','" + studentrecordlist[i].StudentID + "','" + studentrecordlist[i].PaperType + "','" + studentrecordlist[i].QuestionContent + "','" + studentrecordlist[i].RightAnswer + "','" + studentrecordlist[i].TimeStamp + "'";
                    }
                }
                flag = sqlhelper.ExecuteNonQuery(sql, CommandType.Text);
                return flag;
            }
            else
            {
                return 1;
            }
        }
        #endregion

        #region 看看向哪个数据库表中进行填写数据  邱慕夏  2015年11月23日14:26:41
        /// <summary>
        /// 看看向哪个数据库表中进行填写数据
        /// </summary>
        /// <param name="studentrecord"></param>
        /// <returns></returns>
        public String WhichWordRecored(WordQuestionRecordEntity studentrecord)
        {
            DataTable dt ;
            string sql = "select collegeId  from StudentInfoEntity where studentID =@studentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter("@studentID",studentrecord.StudentID ),
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            String which = dt.Rows[0][0].ToString();
            return which;
        }
        #endregion


        #region 查询该考生 是否有资格考试 -赵崇-2015年11月24日 16:39:57
        /// <summary>
        /// 查询该考生 是否有资格考试
        /// </summary>
        /// <param name="pEnStudent">学生</param>
        /// <returns>true表示可以进行考试，false表示 未进行配置 所以不能考试</returns>
        public bool GetIsCanExamByStudent(StudentInfoEntity pEnStudent)
        {

            string tableName = "";
            tableName = "WordQuestionRecordEntity_" + pEnStudent.CollegeID ;

            //1,查询所有的选择题
            DataTable dt;
            string sql = "select * from " + tableName + " where StudentID=@StudentID";
            SqlParameter[] paras = new SqlParameter[]{
                new SqlParameter ("@StudentID",pEnStudent.studentID )
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);

            if (dt.Rows.Count == 0)
            {
                return false;
            }
            return true;
        }
        #endregion


        #region 根据试卷类型查询试卷分数信息——陈新月——2018-04-16
        public DataTable InqueryWordContent(WordQuestionEntity wordQuestion)
        {
            DataTable dt;
            string sql = "select Fration as 分值,QuestionFlag as 题型标志 from WordQuestionEntity where PaperType=@PaperType";
            SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@PaperType",wordQuestion.PaperType)
            };
            dt = sqlhelper.ExecuteQuery(sql, paras, CommandType.Text);
            return dt;
        }
        #endregion

        #region 根据试卷类型更新分数配置——陈新月——2018-04-17
        public int UpdateWordInfo(List<WordQuestionEntity> listWord)
        {
            int UpdateWord = 0;
            for (int i = 0; i < listWord.Count; i++)
            {
                string sql = "update WordQuestionEntity set Fration=@Fration,QuestionFlag=@QuestionFlag where PaperType=@PaperType and QuestionFlag=@QuestionFlag";
                SqlParameter[] paras = new SqlParameter[] {
                new SqlParameter("@PaperType",listWord[i].PaperType),
                new SqlParameter("@Fration",listWord[i].Fration),
                new SqlParameter("@QuestionFlag",listWord[i].QuestionFlag)
                };
                UpdateWord = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);

            }
            return UpdateWord;
        }
        #endregion

        #region 将word表中的Word操作题导入到题库中-于云秀-2018年4月18日10:05:30——陈新月——2018-5-5 17:20:22
        public int InsertwordQuestion(List<WordQuestionEntity> wordquestion)
        {
            //查询所有的word题数目，以此作为题号+1
            DataTable dt = new DataTable();
            string sqlCount = "select * from WordQuestionEntity";
            dt = sqlhelper.ExecuteQuery(sqlCount, CommandType.Text);
            int i = 0;
            int j = 0;
            for (i = 0; i < wordquestion.Count; i++)
            {
                string sql = "insert into WordQuestionEntity(QuestionID,QuestionTypeID,PaperType,Fration,QuestionFlag,QuestionContent,RightAnswer,IsUse,QuestionParameter) values(@questionID,@questionTypeID,@paperType,@fraction,@questionFlag,@questionContent,@rightAnswer,@isUse,@QuestionParameter)";

                wordquestion[i].QuestionID = (double)(dt.Rows.Count + i + 1);
                SqlParameter[] paras = new SqlParameter[] {
                    new SqlParameter("@questionID",wordquestion[i].QuestionID),
                    new SqlParameter("@questionTypeID","3"),
                    new SqlParameter("@paperType",wordquestion[i].PaperType),
                    new SqlParameter("@fraction",wordquestion[i].Fration),
                    new SqlParameter("@questionFlag",wordquestion[i].QuestionFlag),
                    new SqlParameter("@questionContent",wordquestion[i].QuestionContent),
                    new SqlParameter("@rightAnswer",wordquestion[i].RightAnswer),
                    new SqlParameter("@isUse",""),
                     new SqlParameter("@QuestionParameter",wordquestion[i].QuestionParameter)
                };
                try
                {                  
                    //捕获错误数据标志  
                    int flag = sqlhelper.ExecuteNonQuery(sql, paras, CommandType.Text);
                    j++;
                }
                catch (Exception)
                {
                    //返回错误数据的标志
                    return j;
                }
                
            }
            return j;
        }
        #endregion
       


    }
}
